﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Diagnostics;
using HappyTravellingService.Entities;
using System.IO;
using HappyTravellingService.Utils;
using System.Web.Script.Serialization;

namespace HappyTravellingService.DAO
{

    public static class LoyalLog
    {
        static StreamWriter logfile = new StreamWriter("C:\\Log\\log.txt", true);//new StreamWriter("C:\\Log\\log.txt", true);
        public static void log(string line) {
            DateTime now = DateTime.Now;
            string logLine = string.Format("{0} : {1}", now.ToString(), line);
            logfile.Flush();
            logfile.WriteLine(logLine);
            logfile.Flush();

            Debug.WriteLine(line);
        }
    }
    public class DBHelper
    {
        private MySqlConnection connection;
        string connectionString = DBConfig.stringConnection;
        JavaScriptSerializer jss ;

        public DBHelper() {
            LoyalLog.log("\n---------------  "+DateTime.Now+ "  ----------------"); 
            LoyalLog.log("Working from: " + Directory.GetCurrentDirectory());
            connection = new MySqlConnection(connectionString);
            jss = JsonFactory.getJsonSerializer();
            LoyalLog.log("Initializing DB Helper");

        }

        public int CreateUser(string username, string password, string displayname) {

            string qMax = string.Format("Select max(id) as MaxID from user");
            
            Debug.WriteLine("\nQuery: " + qMax);
            LoyalLog.log(qMax);

            int maxId = 0 ;
            int res = 1;
            User user = GetUserByUsername(username);

            // User đã tồn tại trong hệ thống
            if (user != null)
            {
                res = -1;
            }

            // Nếu chưa tồn tại thì cố gắng tạo người dùng mới 
            // và trả về id của người dùng vừa tạo
            // Tạo 1 bảng Friend_Of_Username để lưu bạn bè của người này.
            else
            {
                try
                {
                    connection.Open();
                    MySqlCommand cmd = new MySqlCommand(qMax, connection);
                    MySqlDataReader dataReader = cmd.ExecuteReader();
                    if (dataReader.Read())
                    {
                        // Lấy cái max id để làm kết quả trả về nếu thành công.
                        maxId = (int)dataReader["MaxID"] + 1;
                        res = maxId;
                        LoyalLog.log("Got Max ID: " +maxId);
                    }

                    connection.Close();

                    // Tạo bảng friend
                    connection.Open();
                    // Trường IsFriend trong Bảng Friend:
                    //      0: Mới chỉ là yêu cầu kết bạn
                    //      1: Đã là bạn
                    string qFriend
                        = string.Format("Create table Friends_Of_{0}(friendId int, IsFriend int, primary key(friendId))", username);
                    MySqlCommand cmd2 = new MySqlCommand(qFriend, connection);
                    LoyalLog.log(qFriend);
                    cmd2.ExecuteNonQuery();
                    connection.Close();
                }
                catch (Exception ex)
                {
                    res = -2;
                    LoyalLog.log("Tạo người dùng lỗi: " + ex.Message + "\n" + ex.StackTrace);
                    connection.Close();
                }

                // Chen vao bang user
                try
                {
                    DateTime now = DateTime.Now;
                    string insertQ = string.Format("Insert into user values('{0}','{1}', '{2}','{3}','0','0','{4}')",
                        maxId, username, password, displayname, now.ToString("yyyy-MM-dd HH:mm:ss"));
                    LoyalLog.log("Tao User: " + insertQ);
                    connection.Open();
                    MySqlCommand cmd = new MySqlCommand(insertQ, connection);
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }
                catch (Exception ex)
                {
                    res = -2;
                    LoyalLog.log("Tạo người dùng lỗi: " + ex.Message + "\n" + ex.StackTrace);
                    connection.Close();
                }
                
                
            }

            return res;
        }



        /// <summary>
        /// ///////////////////////////
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public User GetUserByUsername(string uname) {
            string q = string.Format("Select * from user where username='{0}'", uname);
            int res = 0;
            User user = new User();
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(q, connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                if (dataReader.Read())
                {
                    user.id = (int)dataReader["id"];
                    user.Username = uname;
                    user.Password = (string) dataReader["Password"];
                    user.DisplayName = (string)dataReader["DisplayName"];
                    user.Latitude = (double)dataReader["Latitude"];
                    user.Longtitude = (double)dataReader["Longtitude"];
                    user.LastOnline = (DateTime)dataReader["Lastonline"];
                }

                // Nếu KHÔNG có người dùng
                else
                {
                    res = -1;
                }

                connection.Close();
            }
            catch (Exception ex)
            {
                try
                {
                    connection.Close();
                }
                catch (Exception) { }

                LoyalLog.log("\nException: " + ex.Message);
                res = -2;
            }
            if (res == 0)
            {
                return user;
            }
            else
            {
                return null;
            }
            
        }

        public int Login(string username, string password)
        {
            int res = -1;
            string q = string.Format("Select * from user where Username='{0}' and Password='{1}'",
                username, password);
            LoyalLog.log("Login: " + q);
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(q, connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    res = (int)dataReader["Id"];
                }
                connection.Close();

            }
            catch (Exception ex)
            {
                LoyalLog.log("Lỗi login: "+ex.Message+"\n"+ex.StackTrace);
            }

            return res;
        }

        public int CreateMeeting(int leaderId, string content, DateTime time, double latitude, double longtitude)
        {
            int res = -1;


            try
            {
                connection.Open();
                // Lay Id cua Meeting se duoc tao
                int meetingId = -1;
                string maxMeetingIdQuery = string.Format("Select Max(Id) as MaxID from Meeting");
                MySqlCommand midCmd = new MySqlCommand(maxMeetingIdQuery, connection);
                LoyalLog.log("Max Meeting Id: " +maxMeetingIdQuery);
                MySqlDataReader midDataReader = midCmd.ExecuteReader();
                if (midDataReader.Read())
                {
                    meetingId = (int)midDataReader["MaxID"] +1;
                    LoyalLog.log("Got max meeting id: " +meetingId);
                }

                connection.Close();


                // Insert Meeting vao CSDL
                connection.Open();
                string q = string.Format("Insert into Meeting values('{0}', '{1}', '{2}','{3}','{4}','{5}')",
                meetingId, leaderId, content, time.ToString("yyyy-MM-dd HH:mm:ss"), latitude, longtitude);
            
                
                MySqlCommand cmd = new MySqlCommand(q, connection);
                LoyalLog.log("Create Meeting: " + q);
                cmd.ExecuteNonQuery();
                connection.Close();
                

                // Thêm thông tin vào bảng User_Meeting
                connection.Open();
                string addUserMeetingQuery = string.Format("Insert into User_Meeting values('{0}','{1}')",
                    leaderId, meetingId);
                LoyalLog.log(addUserMeetingQuery);
                MySqlCommand insertMtingCmd = new MySqlCommand(addUserMeetingQuery, connection);
                insertMtingCmd.ExecuteNonQuery();
                res = meetingId;
            }
            catch (Exception ex)
            {
                res = -2;
                LoyalLog.log("CreateMeeting: Error: " + ex.Message + "\n" + ex.StackTrace);
            }

            finally
            {
                LoyalLog.log("Closing connection");
                connection.Close();
            }
            return res;
        }  // Create Meeting




        internal int AddFriend(string sender, int targetUserId)
        {
            int res = 0;
            string q = string.Format("Insert into Friends_Of_{0} values('{1}','1')", sender, targetUserId);
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(q, connection);
                LoyalLog.log(q);
                cmd.ExecuteNonQuery();
                res = 1;
            }
            catch (Exception ex)
            {
                res = -2;
                LoyalLog.log(" ERROR: " + ex.Message + "\n" + ex.StackTrace);
            }
            finally { connection.Close(); }
            return res;
        }  // AddFriend


        internal string GetAllFriends(string username)
        {
            string res = "";
            List<User> allFriends = new List<User>();
            List<User> ftemp = new List<User>();
            string q = string.Format("select * from Friends_Of_{0}", username);
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(q, connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read()) {
                    User friend = new User();
                    friend.id = (int)dataReader["friendId"];
                    ftemp.Add(friend);
                }
                connection.Close();

                //TODO: Get full users's information 
                foreach (var fr in ftemp)
                {
                    int id = fr.id;
                    User friend = GetUserById(id);
                    allFriends.Add(friend);
                }


                res = jss.Serialize(allFriends);
            }
            catch (Exception ex)
            {
                res = "-2";
                LoyalLog.log(" ERROR: " + ex.Message + "\n" + ex.StackTrace);
            }
            finally { connection.Close(); }

            return res;
        }



        internal User GetUserById(int id) { 
            string q = string.Format("Select * from user where Id='{0}'", id);
            
            User user = null;
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(q, connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                if (dataReader.Read())
                {
                    user = new User();
                    user.id = (int)dataReader["id"];
                    user.Username = (string) dataReader["Username"];;
                    user.Password = "****";//(string) dataReader["Password"];
                    user.DisplayName = (string)dataReader["DisplayName"];
                    user.Latitude = (double)dataReader["Latitude"];
                    user.Longtitude = (double)dataReader["Longtitude"];
                    user.LastOnline = (DateTime)dataReader["Lastonline"];
                }
                
            }
            catch (Exception ex)
            {
                LoyalLog.log("Get User by ID: Exception: " + ex.Message);
                
            }
            
            finally { connection.Close(); }

            return user;
        }

        internal int EditMeeting(int meetingId, string content, DateTime time, double latitude, double longtitude)
        {
            int res = 0;
            string q 
                = string.Format("Update Meeting set Content='{0}', Time='{1}', Latitude='{2}', Longtitude='{3}' Where Id='{4}'",
                content, time.ToString("yyyy-MM-dd HH:mm:ss"), latitude, longtitude, meetingId);
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(q, connection);
                LoyalLog.log(q);
                cmd.ExecuteNonQuery();
                res = 1;
            }
            catch (Exception ex)
            {
                res = -1;
                LoyalLog.log("EditMeeting ERROR: " + ex.Message + "\n" + ex.StackTrace);
            }
            finally { connection.Close(); }

            return res;
        }

        internal string GetAllMyMeetings(int userId)
        {
            string jsonString = "";
            List<Meeting> meetingsTemp = new List<Meeting>();
            List<Meeting> meetings = new List<Meeting>();
            string q = string.Format("Select * from User_Meeting Where UserId='{0}'", userId);
            LoyalLog.log(q);
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(q, connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    Meeting m = new Meeting();
                    m.Id = (int)dataReader["MeetingId"];
                    meetingsTemp.Add(m);
                }
                connection.Close();

                foreach (Meeting m in meetingsTemp)
                {
                    Meeting m2 = GetMeetingById(m.Id);
                    meetings.Add(m2);
                }

            }
            catch (Exception ex)
            {
                LoyalLog.log("GetAllMyMeetings ERROR: " + ex.Message + "\n" + ex.StackTrace);
            }
            finally { try { connection.Close(); } catch (Exception) { } }

            jsonString = jss.Serialize(meetings);
            return jsonString;
        }

        internal Meeting GetMeetingById(int meetingId)
        {
            Meeting m = null;
            string q = string.Format("Select * from Meeting where Id='{0}'", meetingId);
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(q, connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    User leader = new User();
                    m = new Meeting();
                    m.Id = meetingId;
                    m.Leader = leader;
                    m.Leader.id = (int)dataReader["Leader"];
                    m.Content = (string) dataReader["Content"];
                    m.Latitude = (double)dataReader["Latitude"];
                    m.Longtitude = (double)dataReader["Longtitude"];
                    m.Time = (DateTime)dataReader["Time"];
                }

                connection.Close();

                m.Leader = GetUserById(m.Leader.id);
            }
            catch (Exception ex)
            {
                LoyalLog.log("GetMeetingById ERROR: " + ex.Message + "\n" + ex.StackTrace);
            }
            finally
            {
                try
                {
                    connection.Close();
                }
                catch (Exception) { }
            }

            return m;
        }






        // Tạo ra một lời mời tham gia
        // Tham số:
        //      Người mời, danh sách các khách được mời, cuộc gặp
        internal int Invite(int inviterID, int[] guestIds, int meetingId)
        {
            int res = 0;

            
            try
            {
                MySqlCommand cmd;
                foreach (int guestId in guestIds)
                {
                    connection.Open();
                    string q = string.Format("Insert into Invitations values('{0}','{1}','{2}')",
                        inviterID, guestId, meetingId);
                    LoyalLog.log("Inviting: " +q);
                    cmd = new MySqlCommand(q, connection);
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }

                res = 1;
                
            }
            catch (Exception ex)
            {
                res = -1;
                LoyalLog.log("Invite ERROR: " + ex.Message + "\n" + ex.StackTrace);
            }
            finally
            {
                try
                {
                    // Try to close connection if it was not closed.
                    connection.Close();
                }
                catch (Exception) { }
            }

            return res;
        }

        internal string GetMyInvitationRequest(int userId)
        {
            string jsonStr = "";
            List<Invitation> requets = new List<Invitation>();
            List<Invitation> temp = new List<Invitation>();
            try
            {
                // Lấy ra các ID của các cuộc gặp + ID của những người mời tương ứng
                // Đặt vào List<Invitation>
                connection.Open();
                string q = string.Format("Select * from Invitations where guestId='{0}'", userId);
                LoyalLog.log("GetMyInvitationRequest: "+q);
                MySqlCommand cmd = new MySqlCommand(q, connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    Invitation ivt = new Invitation();
                    User inviter = new User();
                    User invitee = new User();
                    Meeting m = new Meeting();
                    ivt.Inviter = inviter;
                    ivt.Invitee = invitee;
                    ivt.MeetingEvent = m;

                    ivt.Inviter.id = (int)dataReader["InviterID"];
                    ivt.Invitee.id = (int)dataReader["guestId"];
                    ivt.MeetingEvent.Id = (int)dataReader["MeetingId"];
                    temp.Add(ivt);
                }
                connection.Close();
                // Lấy ra thông tin cụ thể của người mời
                foreach (Invitation i2 in temp)
                {
                    i2.Inviter = GetUserById(i2.Inviter.id);
                    i2.Invitee = GetUserById(i2.Invitee.id);

                    // Lấy ra thông tin cụ thể của cuộc hẹn
                    i2.MeetingEvent = GetMeetingById(i2.MeetingEvent.Id);
                }


                

                jsonStr = jss.Serialize(temp);

            }
            catch (Exception ex)
            {
                jsonStr = "-1";
                LoyalLog.log("GetMyInvitationRequest ERROR: " + ex.Message + "\n" + ex.StackTrace);
            }
            finally
            {
                try
                {
                    connection.Close();
                }
                catch (Exception) { }
            }

            return jsonStr;
        }

        internal int ResponeToInvit(int userId, int meetingId, int answer)
        {
            int res = 0;
            
            
            try
            {
                // Xóa lời mời trong bảng Invitations
                // Cứ trả lời thì lời mời đó là hết cmn hiệu lực
                string q = string.Format("Delete from Invitations where guestId='{0}' AND MeetingId='{1}'",
                    userId, meetingId);
                LoyalLog.log("ResponeToInvit: "+q);
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(q, connection);
                cmd.ExecuteNonQuery();


                // Nếu trả lời = 0 (từ chối), thì chả làm gì nữa
                
                // Còn nếu trả lời = 1 thì đặt người dùng này tham gia vào cuộc hẹn
                if (answer == 1)
                {
                    connection.Close();
                    connection.Open();
                    string q2 = string.Format("Insert into User_Meeting values('{0}','{0}')",
                        userId, meetingId);
                    LoyalLog.log("ResponeToInvit: " +q2);
                    cmd = new MySqlCommand(q2, connection);
                    cmd.ExecuteNonQuery();
                }

                res = 1;
            }
            catch (Exception ex)
            {
                res = -1;
                LoyalLog.log(" ERROR: " + ex.Message + "\n" + ex.StackTrace);
            }
            finally
            {
                try
                {
                    connection.Close();
                }
                catch (Exception) { }
            }

            
            return res;
        }

        internal int UpdateLocation(int userId, double latitude, double longtitude)
        {
            int res = 0;

            string q = string.Format("Update User set Latitude = '{0}', Longtitude = '{1}' WHERE Id = '{2}'",
                latitude, longtitude, userId);
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(q, connection);
                LoyalLog.log("UpdateLocation: "+q);
                cmd.ExecuteNonQuery();
                res = 1;
            }
            catch (Exception ex)
            {
                res = -1;
                LoyalLog.log("UpdateLocation ERROR: " + ex.Message + "\n" + ex.StackTrace);
            }
            finally
            {
                try
                {
                    connection.Close();
                }
                catch (Exception) { }
            }

            return res;
        }

        internal List<User> GetMeetingUsers(int meetingId)
        {
            List<User> users = new List<User>();

            string q = string.Format("Select * from User_Meeting where MeetingId='{0}'", meetingId);
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(q, connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                LoyalLog.log("GetMeetingUsers: "+q);
                List<int> userIDs = new List<int>();
                while (dataReader.Read())
                {
                    userIDs.Add((int)dataReader["UserId"]);
                }
                connection.Close();

                foreach  (int uId in userIDs)
                {
                    User u = GetUserById(uId);
                    users.Add(u);
                }
            }
            catch (Exception ex)
            {
                LoyalLog.log(" ERROR: " + ex.Message + "\n" + ex.StackTrace);
            }
            finally
            {
                try
                {
                    connection.Close();
                }
                catch (Exception) { }
            }

            return users;
        }
    }
}


/*
 string q = string.Format("");
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(q, connection);
            }
            catch (Exception ex)
            {
                LoyalLog.log(" ERROR: " + ex.Message + "\n" + ex.StackTrace);
            }
            finally { try
                {
                    connection.Close();
                }
                catch (Exception) { }}
 
 */





